package persistence.db;

import gui.common.SizeUnits;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import model.entity.container.ProductContainer;
import model.entity.container.ProductGroup;
import model.entity.container.StorageUnit;
import model.exceptions.InvalidNameException;
import persistence.ProductContainersDAO;
import common.Result;
import common.Size;
import common.ValueResult;

/**
 * @author tdixon, Paula Chen
 * 
 *         Contains methods for retrieving, adding, updating, and deleting product containers from
 *         a SQL data store.
 */
public class SQLProductContainersDAO implements ProductContainersDAO
{
  private SQLDatabase db;

  public SQLProductContainersDAO(SQLDatabase db)
  {
    this.db = db;
  }

  @Override
  public ValueResult<Set<StorageUnit>> getStorageUnits()
  {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Set<StorageUnit> SUs = new HashSet<StorageUnit>();
    try
    {
      List<ProductGroup> allGroups = getAllProductGroups().getValue();
      String sql = "select id, container_id, name, three_month_measure, "
          + "three_month_unit from product_containers";
      stmt = db.getConnection().prepareStatement(sql);

      rs = stmt.executeQuery();
      while (rs.next())
      {
        if (rs.getInt(2) == 0)
        {
          String name = rs.getString(3);
          StorageUnit newSU = new StorageUnit(name);
          int id = rs.getInt(1);
          newSU.setId(id);
          ValueResult<List<ProductGroup>> childrenResult = getProductGroupsByContainerId(id,
              allGroups);
          if (!childrenResult.getStatus())
          {
            return new ValueResult<Set<StorageUnit>>(false,
                "Something wrong with getting children.");
          }
          List<ProductGroup> children = childrenResult.getValue();
          for (int i = 0; i < children.size(); i++)
          {
            children.get(i).setContainer(newSU);
            Result result = newSU.addProductGroup(children.get(i));
            if (!result.getStatus())
            {
              return new ValueResult<Set<StorageUnit>>(false, "Something wrong with children.");
            }
            children.get(i).setContainer(newSU);
          }
          SUs.add(newSU);
        }
      }
      for (int i = 0; i < allGroups.size(); i++)
      {
        ProductGroup pg = allGroups.get(i);
        ValueResult<List<ProductGroup>> childrenResult = getProductGroupsByContainerId(pg.getId(),
            allGroups);
        if (!childrenResult.getStatus())
        {
          return new ValueResult<Set<StorageUnit>>(false, "Something wrong with getting children.");
        }
        List<ProductGroup> children = childrenResult.getValue();
        for (int j = 0; j < children.size(); j++)
        {
          children.get(j).setContainer(pg);
          Result result = pg.addProductGroup(children.get(j));
          if (!result.getStatus())
          {
            return new ValueResult<Set<StorageUnit>>(false, "Something wrong with children.");
          }
        }
      }
    }
    catch (SQLException e)
    {
      return new ValueResult<Set<StorageUnit>>(false, "SQLException. Message: " + e.getMessage());
    }
    catch (InvalidNameException e)
    {
      return new ValueResult<Set<StorageUnit>>(false, "Invalid name.");
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new ValueResult<Set<StorageUnit>>(false, "SQLException (closing).");
      }
    }
    ValueResult<Set<StorageUnit>> result = new ValueResult<Set<StorageUnit>>(true);
    result.setValue(SUs);
    return result;
  }

  @Override
  public ValueResult<List<ProductGroup>> getProductGroups()
  {
    return new ValueResult<List<ProductGroup>>(false, "Method not supported in this mode.");
  }

  public ValueResult<List<ProductGroup>> getAllProductGroups()
  {

    PreparedStatement stmt = null;
    ResultSet rs = null;
    List<ProductGroup> PGs = new ArrayList<ProductGroup>();
    try
    {
      String sql = "select id, container_id, name, three_month_measure, "
          + "three_month_unit from product_containers";
      stmt = db.getConnection().prepareStatement(sql);

      rs = stmt.executeQuery();
      while (rs.next())
      {
        if (rs.getInt(2) != 0)
        {
          String name = rs.getString(3);
          int id = rs.getInt(1);
          String threeMonthSupplyUnitString = rs.getString(5);
          String Bigletter = threeMonthSupplyUnitString.substring(0, 1);
          String theRest = threeMonthSupplyUnitString.substring(1);
          SizeUnits threeMonthSupplyUnit = SizeUnits.valueOf(Bigletter.toUpperCase() + theRest);
          Size threeMonthSupply = new Size(rs.getFloat(4), threeMonthSupplyUnit);
          ProductGroup newPG = new ProductGroup(name, threeMonthSupply);
          newPG.setId(id);
          PGs.add(newPG);
        }
      }
    }
    catch (SQLException e)
    {
      return new ValueResult<List<ProductGroup>>(false, "SQLException.");
    }
    catch (InvalidNameException e)
    {
      return new ValueResult<List<ProductGroup>>(false, "Invalid name.");
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new ValueResult<List<ProductGroup>>(false, "SQLException (closing).");
      }
    }
    ValueResult<List<ProductGroup>> result = new ValueResult<List<ProductGroup>>(true);
    result.setValue(PGs);
    return result;
  }

  public ValueResult<List<ProductGroup>> getProductGroupsByContainerId(int id,
      List<ProductGroup> allGroups)
  {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    List<ProductGroup> PGs = new ArrayList<ProductGroup>();
    try
    {
      String sql = "select id, container_id, name, three_month_measure, "
          + "three_month_unit from product_containers where container_id = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, id);

      rs = stmt.executeQuery();
      while (rs.next())
      {
        if (rs.getInt(2) != 0)
        {
          for (int i = 0; i < allGroups.size(); i++)
          {
            if (allGroups.get(i).getId() == rs.getInt(1))
            {
              allGroups.get(i).setParent_id(rs.getInt(1));
              PGs.add(allGroups.get(i));
              break;
            }
          }
        }
      }
    }
    catch (SQLException e)
    {
      return new ValueResult<List<ProductGroup>>(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new ValueResult<List<ProductGroup>>(false, "SQLException (closing).");
      }
    }
    ValueResult<List<ProductGroup>> result = new ValueResult<List<ProductGroup>>(true);
    result.setValue(PGs);
    return result;
  }

  @Override
  public Result add(ProductContainer container)
  {
    PreparedStatement stmt = null;
    Statement keyStmt = null;
    ResultSet keyRS = null;
    try
    {
      String sql = "insert into product_containers (container_id, name, "
          + "three_month_measure, three_month_unit) values (?, ?, ?, ?)";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, container.getParent_id());
      stmt.setString(2, container.getName());
      if (container.getClass().getName() == "model.entity.container.ProductGroup")
      {
        ProductGroup PG = (ProductGroup) container;
        stmt.setFloat(3, PG.getThreeMonthSupply().getAmount());
        stmt.setString(4, PG.getThreeMonthSupply().getUnit().toString());
      }
      else
      {
        stmt.setFloat(3, -1);
        stmt.setString(4, null);
      }

      if (stmt.executeUpdate() == 1)
      {
        keyStmt = db.getConnection().createStatement();
        keyRS = keyStmt.executeQuery("select last_insert_rowid()");
        keyRS.next();
        int id = keyRS.getInt(1);
        container.setId(id);
      }
      else
      {
        return new Result(false, "Add Fail.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (keyRS != null)
          keyRS.close();
        if (stmt != null)
          stmt.close();
        if (keyStmt != null)
          keyStmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }

  @Override
  public Result update(ProductContainer container)
  {
    PreparedStatement stmt = null;
    try
    {
      String sql = "update product_containers "
          + "set container_id = ?, name = ?, three_month_measure = ?, three_month_unit = ?"
          + "where id = ?";
      stmt = db.getConnection().prepareStatement(sql);

      stmt.setInt(5, container.getId());
      stmt.setInt(1, container.getParent_id());
      stmt.setString(2, container.getName());
      if (container.getClass().getName() == "model.entity.container.ProductGroup")
      {
        ProductGroup PG = (ProductGroup) container;
        stmt.setFloat(3, PG.getThreeMonthSupply().getAmount());
        stmt.setString(4, PG.getThreeMonthSupply().getUnit().toString());
      }
      else
      {
        stmt.setFloat(3, -1);
        stmt.setString(4, null);
      }
      if (stmt.executeUpdate() != 1)
      {
        return new Result(false, "Update Fail.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }

  @Override
  public Result delete(ProductContainer container)
  {
    PreparedStatement stmt = null;
    try
    {
      String sql = "delete from product_containers where id = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, container.getId());

      if (stmt.executeUpdate() != 1)
      {
        return new Result(false, "Delete fail.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }

  @Override
  public Result saveStorageUnits(Set<StorageUnit> storageUnits)
  {
    return new Result(false, "Method not supported for this persistence type.");
  }

  @Override
  public Result saveProductGroups(List<ProductGroup> productGroups)
  {
    return new Result(false, "Method not supported for this persistence type.");
  }
}
